# coding=utf-8

import cx_Oracle
#import datetime
#import time

#D:\Users\chenchang313\Documents\工作\MySQL研究、引入工作\去IOE\PIF>sqlplus scott/tiger@10.50.120.60:11521/orcl
#
#SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 13 13:28:53 2014

USERNAME = 'scott'
PASSWORD = 'tiger'
DATABASE = '10.50.120.60:11521/orcl'

SQL_TAB_COLS = """select c.table_name,
       c.column_name,
       c.DATA_TYPE,
       c.data_length,
       c.DATA_PRECISION,
       c.DATA_SCALE,
       nullable,
       data_default,
       m.comments
  from dba_tab_columns c
 inner join dba_col_comments m
    on (c.owner = m.owner and c.TABLE_NAME = m.table_name and
       c.COLUMN_NAME = m.column_name)
 where c.owner = '{OWNER}'
   and c.table_name = '{TABLE_NAME}'"""

SQL_PK_COLS = """select c.owner,
       c.table_name,
       c.constraint_name,
       i.column_name,
       i.column_position,
       i.descend
  from dba_constraints c
 inner join dba_ind_columns i
    on (c.index_owner = i.index_owner and c.index_name = i.index_name)
 where c.constraint_type = 'P'
   and c.owner = '{OWNER}'
   and c.table_name = '{TABLE_NAME}'
 order by i.column_position"""

SQL_IND_COLS = """select i.index_name,
       i.column_name,
       i.column_position,
       i.descend,
       d.uniqueness,
       d.index_type
  from dba_constraints c
 right join dba_ind_columns i
    on (c.index_owner = i.index_owner and c.index_name = i.index_name and
       c.constraint_type = 'P')
 inner join dba_indexes d
    on (i.index_owner = d.owner and i.index_name = d.index_name)
 where c.constraint_name is null
   and i.table_owner = '{OWNER}'
   and i.table_name = '{TABLE_NAME}'
 order by i.index_owner, i.index_name, i.column_position"""


data_map = {
    "VARCHAR2" : "VARCHAR",
    "CHAR" : "CHAR",
    "DATE" : "DATETIME",
    "NUMBER" : "DECIMAL",
    "LONG" : "VARCHAR",
    "RAW" : "VARCHAR",
    "CLOB" : "TEXT",
    "BLOB" : "BLOB",
    "FLOAT" : "FLOAT",
}

#| BLOB      |        4 |
#| CHAR      |        1 |
#| CLOB      |       12 |
#| DATE      |      325 |*
#| NUMBER    |      106 |*
#| VARCHAR2  |     1353 |

def default_error_handler(msg):
    return

def get_tab_columns(conn, owner, table_name, error_handler = default_error_handler):
    cursor = conn.cursor()
    cursor.execute(SQL_TAB_COLS.format(OWNER=owner, TABLE_NAME=table_name))
    cols = []
    cmmts = []
    for (table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, data_default, comments) in cursor:
        col = "`" + column_name.lower() + "` " + data_map[data_type]
        
        # 处理NUMBER类型
        if data_type == "NUMBER":
            if data_precision is None:
                data_precision = "65"
            if data_scale is None:
                data_scale = "30"
            col += "(%s, %s)" % (data_precision, data_scale)

        # 处理DATE, CLOB, BLOB类型
        elif data_type in ("DATE", "CLOB", "BLOB"):
            pass
        # 处理CHAR, VARCHAR2类型
        else:
            col += "(%d)" % (data_length)

        # 处理是否可为空
        col += " NULL" if nullable == "Y" else " NOT NULL"

        # 处理默认值
        if data_default is not None:
            if data_default == "sys_guid()":
                data_default = None
                error_handler("Error: {OWNER}.{TABLE_NAME}.{COLUMN_NAME} 的默认值为'sys_guid()'"
                    .format(OWNER=owner, TABLE_NAME=table_name, COLUMN_NAME=column_name))
            elif data_type in ("BLOB", "CLOB"):
                data_default = None
                error_handler("Error: {OWNER}.{TABLE_NAME}.{COLUMN_NAME} 的类型为BLOB/CLOB，MySQL中BLOB/CLOB类型不可以有默认值"
                    .format(OWNER=owner, TABLE_NAME=table_name, COLUMN_NAME=column_name))
            elif data_type in ("DATE"):
                if data_default.lower().find("sysdate") > -1:
                    data_default = None
                    error_handler("Error: {OWNER}.{TABLE_NAME}.{COLUMN_NAME} 的类型为DATE，默认值为sysdate，MySQL中不可以用函数做为默认值"
                        .format(OWNER=owner, TABLE_NAME=table_name, COLUMN_NAME=column_name))

        if data_default is not None:
            col += " default " + data_default#.replace("'", "'")

        # 处理注释
        if comments is not None:
            col += " comment '" + comments.replace("'", "''") + "'"

        cols.append(col)
    cursor.close()

    return cols

def get_tab_primarykey(conn, owner, table_name):
    cursor = conn.cursor()
    cursor.execute(SQL_PK_COLS.format(OWNER=owner, TABLE_NAME=table_name))
    cols = []
    for (owner, table_name, constraint_name, column_name, column_position, descend) in cursor:
        cols.append("`" + column_name.lower() + "`")
    cursor.close()
    return cols

def get_tab_indexes(conn, owner, table_name):
    cursor = conn.cursor()
    cursor.execute(SQL_IND_COLS.format(OWNER=owner, TABLE_NAME=table_name))
    idxs = []
    idx = None
    old_index_name = ""
    for (index_name, column_name, column_position, descend, uniqueness, index_type) in cursor:
        if index_name != old_index_name:
            old_index_name = index_name
            if idx is not None:
                idxs.append(idx)
            idx = [index_name, [], uniqueness, index_type]
        idx[1].append(column_name)
    cursor.close()
    if idx is not None:
        idxs.append(idx)

    # [[index_name, [column_name, column_name, ...], uniqueness, index_type]
    # [[index_name, [column_name, column_name, ...], uniqueness, index_type]
    # ...
    return idxs

def get_tab_definition(conn, owner, table_name, error_handler = default_error_handler):
    tabcols = get_tab_columns(conn, owner, table_name, error_handler)
    tabpk = get_tab_primarykey(conn, owner, table_name)
    tabcomm = None

    cursor = conn.cursor()
    cursor.execute("""select owner, table_name, comments
  from dba_tab_comments
 where owner = '{OWNER}'
   and table_name = '{TABLE_NAME}'""".format(OWNER=owner, TABLE_NAME=table_name))
    for (owner, table_name, commnents) in cursor:
        tabcomm = commnents
    cursor.close()

    if tabpk:
        tabcols.append("primary key (" + ", ".join(tabpk) + ")")
    else:
        error_handler("Warning: {OWNER}.{TABLE_NAME}表没有主键".format(OWNER=owner, TABLE_NAME=table_name))

    tabsql = "create table `" + table_name.lower() +"""`
(
    """ + ",\n    ".join(tabcols) + """
) engine=innodb default character set=utf8mb4 default collate=utf8mb4_bin"""
    if tabcomm is not None:
        tabsql += " comment='"+ tabcomm +"'"""
    tabsql += ";\n"

    return tabsql

def get_tab_modification(conn, owner, table_name):
    idxs = get_tab_indexes(conn, owner, table_name)
    if not idxs:
        return ""
    tabsql = "alter table `" + table_name.lower() + "` " + \
        ",\n    ".join(["add" + (" unique" if uniq == "UNIQUE" else "") + " index `" + idx.lower() + "`(" \
            + ",".join([ "`%s`" % col.lower() for col in  cols]) \
        + ")" \
        for (idx, cols, uniq, idx_type) in idxs]) + ";\n"

    return tabsql 

def test_db():
    conn = cx_Oracle.connect(USERNAME, PASSWORD, DATABASE)

#    print(get_tab_columns(conn, "SCOTT", "TX"))
#    print(get_tab_primarykey(conn, "SCOTT", "TX"))
#    print(get_tab_indexes(conn, "SCOTT", "EMP"))
    print(get_tab_definition(conn, "SCOTT", "TX"))
    print(get_tab_modification(conn, "SCOTT", "TX"))
    print(get_tab_definition(conn, "SCOTT", "EMP"))
    print(get_tab_modification(conn, "SCOTT", "EMP"))

    conn.close()

def test():
    print ('test')
    test_db()

if __name__ == "__main__":
    test()
